package zy.dao.base.emp.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.emp.EmpDAO;
import zy.dto.base.emp.EmpLoginDto;
import zy.entity.base.emp.T_Base_Emp;
import zy.form.StringForm;
import zy.util.StringUtil;
@Repository
public class EmpDAOImpl extends BaseDaoImpl implements EmpDAO {

	@Override
	public Integer count(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object dm_code = param.get("dm_code");
		Object em_type = param.get("em_type");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_emp t");
		sql.append(" where 1 = 1");
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.em_code,:searchContent)>0 OR INSTR(t.em_name,:searchContent)>0)");
        }
        if(null != dm_code && !"".equals(dm_code)){
        	sql.append(" and t.em_dm_code = :dm_code");
        }
        if(StringUtil.isNotEmpty(em_type)){
        	sql.append(" AND t.em_type=:em_type");
        }
        sql.append(" AND (em_login_shop=:shop_code OR em_shop_code=:shop_code)");
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Emp> list(Map<String, Object> param) {
		Object dm_code = param.get("dm_code");
		Object em_type = param.get("em_type");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select em_id,em_code,em_name,em_sex,em_marry,em_cardid,em_mobile,em_dm_code,em_addr,");
		sql.append("em_birthday,em_state,em_workdate,em_indate,em_outdate,em_shop_code,em_type,em_img,em_pass,em_royaltyrate,");
		sql.append("(select dm_name from t_base_department dm where dm.dm_code=t.em_dm_code and dm.companyid=t.companyid) as em_dm_name,");
		sql.append("sp_name as em_shop_name, ");
		sql.append(" (SELECT COUNT(1) FROM t_base_emp_reward er WHERE er_em_code = em_code AND er.companyid = t.companyid) AS reward_count");
		sql.append(" from t_base_emp t");
		sql.append(" JOIN t_base_shop sp");
		sql.append(" ON sp.sp_code = t.em_shop_code");
		sql.append(" AND sp.companyid = t.companyid");
		sql.append(" where 1 = 1");
		if(null != searchContent && !"".equals(searchContent)){
			sql.append(" AND (INSTR(t.em_code,:searchContent)>0 OR INSTR(t.em_name,:searchContent)>0)");
	    }
        if(null != dm_code && !"".equals(dm_code)){
        	sql.append(" AND t.em_dm_code = :dm_code");
        }
        if(StringUtil.isNotEmpty(em_type)){
        	sql.append(" AND t.em_type=:em_type");
        }
//        sql.append(" AND (em_login_shop=:shop_code OR em_shop_code=:shop_code)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" order by :sidx :sord");
		sql.append(" limit :start,:end");
		List<T_Base_Emp> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Emp.class));
		return list;
	}
	@Override
	public Integer countShop(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
//		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_emp t");
		sql.append(" where 1 = 1");
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.em_code,:searchContent)>0 OR INSTR(t.em_name,:searchContent)>0)");
        }
        /*if(null != shop_type && !CommonUtil.FOUR.equals(shop_type)){
        	sql.append(" AND em_login_shop=:shop_upcode ");
        }else{
        }*/
        sql.append(" AND em_shop_code=:shop_code");
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Emp> listShop(Map<String, Object> param) {
//		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select em_id,em_code,em_name,em_sex,em_marry,em_cardid,em_mobile,em_dm_code,em_addr,");
		sql.append("em_birthday,em_state,em_workdate,em_indate,em_outdate,em_shop_code,em_type,em_img,em_pass,em_royaltyrate,");
//		sql.append("(select dm_name from t_base_department dm where dm.dm_code=t.em_dm_code and dm.companyid=t.companyid) as em_dm_name,");
		sql.append("(select sp_name from t_base_shop sp where sp.sp_code=t.em_shop_code and sp.companyid=t.companyid) as em_shop_name ");
		sql.append(" from t_base_emp t");
		sql.append(" where 1 = 1");
		if(null != searchContent && !"".equals(searchContent)){
			sql.append(" AND  INSTR(t.em_name,:searchContent)>0");
	    }
		/*if(null != shop_type && !CommonUtil.FOUR.equals(shop_type)){
        	sql.append(" AND em_login_shop=:shop_upcode ");
        }else{
        }*/        
		sql.append(" AND em_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" order by :sidx :sord");
		sql.append(" limit :start,:end");
		List<T_Base_Emp> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Emp.class));
		return list;
	}
	@Override
	public void save(T_Base_Emp emp) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(em_code+0)) from t_base_emp ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(emp), String.class);
		emp.setEm_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_emp");
		sql.append(" (em_code,em_name,em_sex,em_marry,em_cardid,em_mobile,em_dm_code,em_addr,em_birthday,");
		sql.append("em_state,em_workdate,em_indate,em_shop_code,em_login_shop,em_type,em_pass,em_royaltyrate,companyid)");
		sql.append(" VALUES(:em_code,:em_name,:em_sex,:em_marry,:em_cardid,:em_mobile,:em_dm_code,:em_addr,:em_birthday,");
		sql.append(":em_state,:em_workdate,:em_indate,:em_shop_code,:em_login_shop,:em_type,:em_pass,:em_royaltyrate,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(emp),holder);
		int id = holder.getKey().intValue();
		emp.setEm_id(id);
	}

	@Override
	public T_Base_Emp queryByID(Integer em_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select em_id,em_code,em_name,em_sex,em_marry,em_cardid,em_mobile,em_dm_code,em_addr,");
		sql.append("em_birthday,em_state,em_workdate,em_indate,em_outdate,em_shop_code,em_type,em_img,em_pass,em_royaltyrate,");
		sql.append("(select dm_name from t_base_department dm where dm.dm_code=t.em_dm_code and dm.companyid=t.companyid) as em_dm_name,");
		sql.append("(select sp_name from t_base_shop sp where sp.sp_code=t.em_shop_code and sp.companyid=t.companyid) as em_shop_name ");
		sql.append(" from t_base_emp t");
		sql.append(" WHERE t.em_id=:em_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("em_id", em_id),new BeanPropertyRowMapper<>(T_Base_Emp.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void update(T_Base_Emp emp) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_emp");
		sql.append(" SET em_name=:em_name");
		sql.append(" ,em_sex=:em_sex");
		sql.append(" ,em_marry=:em_marry");
		sql.append(" ,em_cardid=:em_cardid");
		sql.append(" ,em_mobile=:em_mobile");
		sql.append(" ,em_dm_code=:em_dm_code");
		sql.append(" ,em_addr=:em_addr");
		sql.append(" ,em_birthday=:em_birthday");
		sql.append(" ,em_state=:em_state");
		sql.append(" ,em_workdate=:em_workdate");
		sql.append(" ,em_indate=:em_indate");
		sql.append(" ,em_outdate=:em_outdate");
		sql.append(" ,em_shop_code=:em_shop_code");
		sql.append(" ,em_type=:em_type");
		sql.append(" ,em_royaltyrate=:em_royaltyrate");
		sql.append(" WHERE em_id=:em_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(emp));
	}

	@Override
	public void del(Integer em_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_emp");
		sql.append(" WHERE em_id=:em_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("em_id", em_id));
	}

	@Override
	public List<T_Base_Emp> listCombo(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select em_code,em_name");
		sql.append(" from t_base_emp t");
		sql.append(" WHERE em_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param,new BeanPropertyRowMapper<>(T_Base_Emp.class));
	}

	@Override
	public List<StringForm> _listShop(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select em_code AS code,em_name AS name");
		sql.append(" from t_base_emp t");
		sql.append(" WHERE em_shop_code=:shop_code");
		sql.append(" AND em_type=0");//只查导购员类型
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param,new BeanPropertyRowMapper<>(StringForm.class));
	}

	@Override
	public EmpLoginDto login(String em_code, String em_pass, String co_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT em_id,em_code,em_pass,em_name,em_mobile,em_shop_code,sp_name em_shop_name,t.companyid,co_code,");
		sql.append(" sp_upcode AS shop_upcode,sp_shop_type AS shoptype,");
		sql.append(" (select sp_shop_type FROM t_base_shop s1 WHERE s1.sp_code=sp.sp_upcode AND s1.companyid=sp.companyid LIMIT 1) AS shop_uptype");
		sql.append(" FROM t_base_emp t");
		sql.append(" JOIN t_base_shop sp ON sp_code = em_shop_code AND t.companyid = sp.companyid");
		sql.append(" JOIN t_sys_company co ON sp.companyid = co_id");
		sql.append(" WHERE 1=1");
		sql.append(" AND em_type=0");
		sql.append(" AND em_pass = :em_pass");
		sql.append(" AND em_code = :em_code");
		sql.append(" AND co_code = :co_code");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("co_code", co_code).addValue("em_code", em_code).addValue("em_pass", em_pass),
					new BeanPropertyRowMapper<>(EmpLoginDto.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void updatePwd(T_Base_Emp emp) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_emp");
		sql.append(" SET em_pass=:em_pass");
		sql.append(" WHERE em_id=:em_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(emp));
	}
	

}
